* price per sqm used dwellings BCN - data from city council
********************************************************************************
* prelims
********************************************************************************
{
clear all
cap log close
set more off
}
********************************************************************************
* clean data
********************************************************************************
{
	* CDIS
	****************************************************************************
import excel "$orig/aj_barcelona/price_useddwellings_neigh.xlsx", sheet("CDIS") cellrange(A2:H234) firstrow allstring clear

* clean
rename A CDIS
drop B
rename C year
rename D Q1
rename E Q2
rename F Q3
rename G Q4
rename H trendbreak

drop if _n==1

destring year Q1-Q4, force replace

replace CDIS = "0" + CDIS if length(CDIS)==1

* average price
egen pricesqm = rowmean(Q1-Q4)

drop if trendbreak == "break trend"

gsort CDIS year -trendbreak
duplicates drop CDIS year, force

* store
compress
save "$data/int/ajbcn_houseprice_cdis.dta", replace


	* BARRI
	****************************************************************************
import excel "$orig/aj_barcelona/price_useddwellings_neigh.xlsx", sheet("BARRI") cellrange(A3:J76) firstrow clear

* clean
drop DtoBarrios

destring pricesqm*, force replace

tostring CDIS, replace
replace CDIS = "0" + CDIS if length(CDIS)==1

* reshape
reshape long pricesqm, i(CDIS BARRI) j(year)
la var pricesqm "price sqm (sale)"

* pricesqm imputed
g priceimputed = (pricesqm == . )
la var priceimputed "price imputed from district"

rename pricesqm t_pricesqm

* add district price if missing
merge m:1 year CDIS using "$data/int/ajbcn_houseprice_cdis.dta", keepusing(pricesqm)
drop if _m==2
drop _m

replace t_pricesqm = pricesqm if t_pricesqm ==. & pricesqm !=.
drop pricesqm
rename t_pricesqm pricesqm

tostring BARRI, replace
replace BARRI = "0" + BARRI if length(BARRI)==1

* store
compress
save "$data/int/ajbcn_houseprice_barri.dta", replace
}
********************************************************************************
* clean data
********************************************************************************
{
cap log close
clear
}

